<?php
/**
 * excel导入导出类
 * Yiiexcel.php
 * ==============================================
 * 版权所有 2013-2013
 * ----------------------------------------------
 * 这不是一个自由软件，未经授权不许任何使用和传播。
 * ----------------------------------------------
 * @date: 2013-10-14 下午4:48:36
 * @author: netljc
 * @email:netliujuncheng111@126.com 
 * ==============================================
 * 导入时获取数据
 * Yii::app()->excel->readSheet('test.xls');
 * 导出数据时获取数据
 * $data = array(
 *	            array(1,'张三',32,'男'),
 *	            array(1,'李四',30,'男')
 *            );
 * Yii::app()->excel->exportSheet($data,array('编号','姓名','年龄','性别'),'Excel5');
*/

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . Yii::app()->basePath.'/extensions/yiiexcel/Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

class Yiiexcel
{
    public $mimeTypes = array(
            'Excel5' => array(
                    'Content-type'=>'application/vnd.ms-excel',
                    'extension'=>'xls',
            ),
            'Excel2007'	=> array(
                    'Content-type'=>'application/vnd.ms-excel',
                    'extension'=>'xlsx',
            ),
            'PDF' =>array(
                    'Content-type' => 'application/pdf',
                    'extension'=>'pdf',
            ),
            'HTML' =>array(
                    'Content-type'=>'text/html',
                    'extension'=>'html',
            ),
            'CSV' =>array(
                    'Content-type'=>'application/csv',
                    'extension'=>'csv',
            )
    );
    public function init(){
        
    }
    //读取指定的工作表
	public function readSheet($file, $index="0")
	{
		$objPHPExcel = PHPExcel_IOFactory::load($file);
		$objPHPExcel->setActiveSheetIndex($index);
		$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

		return $sheetData;
	}
	/**
	 * 导出Excel表
	 * @param unknown_type $data 要导出的数据
	 * @param unknown_type $title 表头
	 * @param unknown_type $format 格式 Excel5 Excel2007 HTML CSV
     * @param unknown_type $filename 文件名称
     * @param unknown_type $sheets 新建表的数组
	 */
	public function exportSheet($data, $title='', $format='Excel5', $filename, $sheets) {
	    $objPHPExcel = new PHPExcel();

        // 设置文档属性
        $objPHPExcel->getProperties()->setCreator("leshisz");
        
        // 循环处理表的建立
        if (!isset($sheets)) {
            $sheets = array(0=>'总表');
        }
        
        foreach ($sheets as $key => $value) {
            if ($key != 0) {
                // 新建表
                $objPHPExcel->createSheet($key);
            }
            // 设置表头
            $row = 1;
            if(!empty($title)){
                $title_len = count($title);
                $i=0;
                foreach ($title as $col){
                  $i++;
                  $cell = $this->columnName($i).$row;
                  $objPHPExcel->setActiveSheetIndex($key)->setCellValue($cell,$col);
                }
            }
            // 中间部分
            if(!empty($data)){
                foreach ($data as $rows){
                    $i=0;
                    if ($key != 0) {
                        if ($rows[$value[0]] == $value[1]) {
                            $row++;
                            foreach ($rows as $col){
                                $i++;
                                $cell = $this->columnName($i).$row;
                                $objPHPExcel->setActiveSheetIndex($key)->setCellValue($cell,$col);
                            }
                        }
                    } else {
                        $row++;
                        foreach ($rows as $col){
                            $i++;
                            $cell = $this->columnName($i).$row;
                            $objPHPExcel->setActiveSheetIndex($key)->setCellValue($cell,$col);
                        }
                    }
                }
            }
            
            // Rename worksheet
            if ($key != 0) {
                $objPHPExcel->getActiveSheet()->setTitle($value[1]);
            } else {
                $objPHPExcel->getActiveSheet()->setTitle($value);
            }
            $objPHPExcel->setActiveSheetIndex($key);
        }
        
        // 输出放入文件名称
        if (!isset($filename)) {
            $filename = time();
        }        
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
		header('Content-type: ' . $this->mimeTypes[$format]['Content-type']);
		header('Content-Disposition: attachment; filename="'.$filename.'.'.$this->mimeTypes[$format]['extension'].'"');
		header('Cache-Control: max-age=0');
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $format);
		$objWriter->save('php://output');
	}
	
	//计算单元格的列标
	public function columnName($index)
	{
	    --$index;
	    if($index >= 0 && $index < 26)
	        return chr(ord('A') + $index);
	    else if ($index > 25)
	        return ($this->columnName($index / 26)).($this->columnName($index%26 + 1));
	    else
	        throw new Exception("Invalid Column # ".($index + 1));
	}
}